import logging
from datetime import datetime
from typing import List, Optional

from fastapi import APIRouter, Query, Path
from fastapi.params import Depends
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

scene_head_router = APIRouter(prefix="/material/sceneHead", tags=["储物柜报警设定管理"],
                              dependencies=[Depends(oauth2_scheme)])


class SceneSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    fscenetype: str = Field(None, description="报警类型")


@scene_head_router.post("/list", summary="查询储物柜报警设定")
def list_scene(scene: SceneSearch = Query(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            param = {}
            sql = "select count(1) from i_ts_scene_head "
            sql = sql + " where 1=1"
            if scene.fsiteNo is not None:
                sql = sql + " and fsite_no= :fsiteNo "
                param["fsiteNo"] = scene.fsiteNo
            count = db.execute(text(sql), param).scalar()

            param = {}
            sql = "select fsceneno,fscenename,fsite_no,fstorageflag,fexectime,"
            sql = sql + "fexecweek1,fexecweek2,fexecweek3,fexecweek4,fexecweek5,"
            sql = sql + "fexecweek6,fexecweek7,fscenetype,foperator,fupddate"
            sql = sql + " from i_ts_scene_head "
            sql = sql + " where 1=1"
            if scene.fsiteNo is not None:
                sql = sql + " and fsite_no= :fsiteNo "
                param["fsiteNo"] = scene.fsiteNo
            if scene.order == "desc":
                sql = sql + " order by fsceneno desc "
            else:
                sql = sql + " order by fsceneno asc "
            sql = sql + " limit :limit offset :offset "
            param["limit"] = scene.limit
            param["offset"] = scene.offset
            data = db.execute(text(sql), param).fetchall()

            # details 明细列表
            param = {}
            sql_3 = " select b.fsceneno,b.fno,b.fairitem,b.fwhere,b.fvalue,b.fwhere2,b.fvalue2 "
            sql_3 = sql_3 + " from i_ts_scene_head a"
            sql_3 = sql_3 + " inner join i_ts_scene_item b on a.fsceneno=b.fsceneno"
            sql_3 = sql_3 + " where 1=1 "
            if scene.fsiteNo is not None:
                sql_3 = sql_3 + " and a.fsite_no= :fsiteNo "
                param["fsiteNo"] = scene.fsiteNo
            sql_3 = sql_3 + " order by b.fsceneno,b.fno"
            data_detail = db.execute(text(sql_3), param).fetchall()

            data_list = []
            for key in range(len(data)):
                fupddate = ""
                try:
                    fupddate = data[key][14].strftime('%Y-%m-%dT%H:%M:%S')
                except Exception as e:
                    print(e)

                if (fupddate == ""):
                    fupddate = None

                d1 = {}
                d1['fsceneno'] = data[key][0]
                d1['fscenename'] = data[key][1]
                d1['fsiteNo'] = data[key][2]
                d1['fstorageflag'] = data[key][3]
                d1['fexectime'] = data[key][4]
                d1['fexecweek1'] = data[key][5]
                d1['fexecweek2'] = data[key][6]
                d1['fexecweek3'] = data[key][7]
                d1['fexecweek4'] = data[key][8]
                d1['fexecweek5'] = data[key][9]
                d1['fexecweek6'] = data[key][10]
                d1['fexecweek7'] = data[key][11]
                d1['fscenetype'] = data[key][12]
                d1['foperator'] = data[key][13]
                d1['fupddate'] = fupddate
                sbillno = data[key][0]

                details = []
                for j1 in range(len(data_detail)):
                    if (str(data_detail[j1][0]) == str(sbillno)):
                        d2 = {}
                        d2['fsceneno'] = data_detail[j1][0]
                        d2['fno'] = data_detail[j1][1]
                        d2['fairitem'] = data_detail[j1][2]
                        d2['fwhere'] = data_detail[j1][3]
                        d2['fvalue'] = data_detail[j1][4]
                        d2['fwhere2'] = data_detail[j1][5]
                        d2['fvalue2'] = data_detail[j1][6]
                        details.append(d2)

                d1["details"] = details
                data_list.append(d1)

            info_dict = {"list": data_list,
                         "limit": scene.limit,
                         "page": scene.pageIndex,
                         "pages": (count + scene.limit - 1) // scene.limit,
                         "total": count}
            return BaseResponse.success(data=info_dict)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class SceneItem(BaseModel):
    """储物柜报警设定超标设备明细"""
    fairitem: int = Field(..., description="设备项编号")
    fwhere: str = Field(..., description="条件1")
    fvalue: str = Field(..., description="值1")
    fwhere2: str = Field(..., description="条件2")
    fvalue2: str = Field(..., description="值2")


class LockerAlarmCreate(BaseModel):
    """储物柜报警设定主表创建模型"""
    fscenename: str = Field(..., description="场景名称")
    fscenetype: int = Field(..., description="场景类型")
    fsiteNo: str = Field(..., description="站点编号")
    fstorageflag: int = Field(..., description="存储标志")

    fexectime: str = Field("1111111", description="执行时间")
    fexecweek1: str = Field("1", description="周一执行标志")
    fexecweek2: str = Field("1", description="周二执行标志")
    fexecweek3: str = Field("1", description="周三执行标志")
    fexecweek4: str = Field("1", description="周四执行标志")
    fexecweek5: str = Field("1", description="周五执行标志")
    fexecweek6: str = Field("1", description="周六执行标志")
    fexecweek7: str = Field("1", description="周日执行标志")

    details: List[SceneItem] = Field([], description="超标设备列表")

    # 自动填充字段
    fsceneno: Optional[int] = Field(None, description="场景单号（自动生成）")
    foperator: Optional[str] = Field(None, description="操作人ID")
    fupddate: datetime = Field(datetime.now(), description="更新时间")


@scene_head_router.post("/create", summary="新增储物柜报警设定")
def create_scene(scene: LockerAlarmCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        d1 = {}
        with db.begin():
            fsceneno = 0
            sql = "select nextval('s_tc_scene_head_fsceneno_seq') as last_value"
            res = db.execute(text(sql)).scalar()
            fsceneno = res

            sql = "insert into i_ts_scene_head(fsceneno,fscenename,fsite_no,fstorageflag,fexectime,fexecweek1,fexecweek2,fexecweek3,fexecweek4,fexecweek5,fexecweek6,fexecweek7,fscenetype,foperator,fupddate)"
            sql = sql + " values( :fsceneno , :fscenename , :fsiteNo , :fstorageflag , :fexectime , :fexecweek1 , :fexecweek2 , :fexecweek3 , :fexecweek4 , :fexecweek5 , :fexecweek6 , :fexecweek7 , :fscenetype , :foperator ,now());"
            db.execute(text(sql), {
                "fsceneno": fsceneno,
                "fscenename": scene.fscenename,
                "fsiteNo": scene.fsiteNo,
                "fstorageflag": scene.fstorageflag,
                "fexectime": scene.fexectime,
                "fexecweek1": scene.fexecweek1,
                "fexecweek2": scene.fexecweek2,
                "fexecweek3": scene.fexecweek3,
                "fexecweek4": scene.fexecweek4,
                "fexecweek5": scene.fexecweek5,
                "fexecweek6": scene.fexecweek6,
                "fexecweek7": scene.fexecweek7,
                "fscenetype": scene.fscenetype,
                "foperator": user_id,
            })

            # 超标设备列表
            iList = 1
            details = scene.details
            for detail in details:
                sql = "insert into i_ts_scene_item(fsceneno,fairitem,fwhere,fvalue,fwhere2,fvalue2)"
                sql = sql + " values(:fsceneno , :fairitem , :fwhere , :fvalue , :fwhere2 , :fvalue2 );"
                db.execute(text(sql), {
                    "fsceneno": fsceneno,
                    "fairitem": detail.fairitem,
                    "fwhere": detail.fwhere,
                    "fvalue": detail.fvalue,
                    "fwhere2": detail.fwhere2,
                    "fvalue2": detail.fvalue2,
                })
                iList = iList + 1

            # 返回
            d1['fsceneno'] = fsceneno
            d1['fscenename'] = scene.fscenename
            d1['fsiteNo'] = scene.fsiteNo
            d1['fstorageflag'] = scene.fstorageflag
            d1['fexectime'] = scene.fexectime
            d1['fexecweek1'] = scene.fexecweek1
            d1['fexecweek2'] = scene.fexecweek2
            d1['fexecweek3'] = scene.fexecweek3
            d1['fexecweek4'] = scene.fexecweek4
            d1['fexecweek5'] = scene.fexecweek5
            d1['fexecweek6'] = scene.fexecweek6
            d1['fexecweek7'] = scene.fexecweek7
            d1['fscenetype'] = scene.fscenetype
            d1['foperator'] = scene.foperator
            d1['fupddate'] = None
            d1["details"] = details
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@scene_head_router.post("/update", summary="修改报警设定")
def update_scene(scene: LockerAlarmCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        d1 = {}
        with db.begin():
            sql = "update i_ts_scene_head"
            sql = sql + " set fscenename= :fscenename ,fstorageflag= :fstorageflag "
            sql = sql + ",fexectime= :fexectime ,fexecweek1= :fexecweek1 ,fexecweek2= :fexecweek2 ,fexecweek3= :fexecweek3 ,fexecweek4= :fexecweek4 "
            sql = sql + "',fexecweek5= :fexecweek5 ,fexecweek6= :fexecweek6 ,fexecweek7= :fexecweek7 "
            sql = sql + "',fscenetype= :fscenetype ,foperator= :foperator ,fupddate=now()"
            sql = sql + " where fsceneno= :fsceneno  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fscenename": scene.fscenename,
                "fstorageflag": scene.fstorageflag,
                "fexectime": scene.fexectime,
                "fexecweek1": scene.fexecweek1,
                "fexecweek2": scene.fexecweek2,
                "fexecweek3": scene.fexecweek3,
                "fexecweek4": scene.fexecweek4,
                "fexecweek5": scene.fexecweek5,
                "fexecweek6": scene.fexecweek6,
                "fexecweek7": scene.fexecweek7,
                "fscenetype": scene.fscenetype,
                "foperator": user_id,
                "fsceneno": scene.fsceneno,
                "fsiteNo": scene.fsiteNo,
            })

            # 超标设备列表
            sql = "delete from i_ts_scene_item where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": scene.fsceneno,
            })

            iList = 1
            # 超标设备列表
            iList = 1
            details = scene.details
            for detail in details:
                sql = "insert into i_ts_scene_item(fsceneno,fairitem,fwhere,fvalue,fwhere2,fvalue2)"
                sql = sql + " values(:fsceneno , :fairitem , :fwhere , :fvalue , :fwhere2 , :fvalue2 );"
                db.execute(text(sql), {
                    "fsceneno": scene.fsceneno,
                    "fairitem": detail.fairitem,
                    "fwhere": detail.fwhere,
                    "fvalue": detail.fvalue,
                    "fwhere2": detail.fwhere2,
                    "fvalue2": detail.fvalue2,
                })
                iList = iList + 1
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@scene_head_router.post("/delete/{fsceneno}", summary="删除储物柜报警设定")
def delete_scene(fsceneno: int = Path(description="编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from i_ts_scene_head where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno,
            })
            sql = "delete from i_ts_scene_item where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
